package com.mzj.saas.mservice.community.service;

import com.alibaba.fastjson.JSONObject;
import com.mzj.saas.commons.ApiData;
import com.mzj.saas.commons.ResultBean;
import com.mzj.saas.commons.vo.redis.TokenRedisVo;
import com.mzj.saas.mservice.community.entity.CommunityBuild;
import com.mzj.saas.mservice.community.entity.HouseSurrender;
import com.mzj.saas.mservice.community.repository.CommunityBuildRepository;
import com.mzj.saas.mservice.community.repository.CommunityRepository;
import com.mzj.saas.mservice.community.repository.HouseSurrenderRepository;
import com.mzj.saas.mservice.community.vo.HouseSurrenderVO;
import com.mzj.saas.mservice.department.entity.Department;
import com.mzj.saas.mservice.department.repository.DepartmentRepository;
import com.mzj.saas.mservice.eligible.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.mservice.eligible.entity.DepartmentHierarchy;
import com.mzj.saas.mservice.eligible.repository.DepartmentEnterpriseTagRefRepository;
import com.mzj.saas.mservice.eligible.repository.DepartmentHierarchyRepository;
import com.mzj.saas.mservice.house.entity.House;
import com.mzj.saas.mservice.house.repository.HouseRepository;
import com.mzj.saas.mservice.house.vo.HouseVO;
import com.mzj.saas.mservice.house.vo.OrgVO;
import com.mzj.saas.mservice.property.entity.CommunityHouseUser;
import com.mzj.saas.mservice.property.entity.MatchUser;
import com.mzj.saas.mservice.property.entity.MatchUserHouse;
import com.mzj.saas.mservice.property.repository.CommunityHouseUserRepository;
import com.mzj.saas.mservice.property.repository.MatchUserHouseRepository;
import com.mzj.saas.mservice.property.repository.MatchUserRepository;
import com.mzj.saas.mservice.property.service.CommunityHouseUserService;
import com.mzj.saas.mservice.property.vo.MatchUserHouseVO;
import com.mzj.saas.mservice.property.vo.SaveOrEditVO;
import com.mzj.saas.mservice.sys.repository.UserRepository;
import com.mzj.saas.mservice.sys.utils.SqlUtils;
import com.mzj.saas.redis.RedisService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

@Service
public class AppCommunityHouseService {
    private static final Logger log = LoggerFactory.getLogger(AppCommunityHouseService.class);

    @Autowired
    private RedisService redisService;
    @Autowired
    private CommunityHouseUserService communityHouseUserService;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private HouseSurrenderRepository houseSurrenderRepository;
    @Autowired
    private CommunityHouseUserRepository communityHouseUserRepository;
    @Autowired
    private MatchUserHouseRepository matchUserHouseRepository;
    @Autowired
    private MatchUserRepository matchUserRepository;
    @Autowired
    private AppCommunityService appCommunityService;

    /**
     * 查询对应标签的物业房屋列表
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param vo
     * @param houseIdList
     * @return
     */
    public ResultBean<ApiData<HouseVO>> pageslist(String accessToken, Integer pageNumber, Integer pageSize, HouseVO vo, List<String> houseIdList) {
        log.info("CommunityHouseService pageslist accessToken={},pageNumber={},pageSize={},vo={}", accessToken, pageNumber, pageSize, vo.toString());

        try {
            // 登录校验
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }

            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            Long id = hierarchy.getId();
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct().collect(Collectors.toList());// 用户企业标签

            // //1、政府机构，根据区域标签所对应区域id查
            Long areaTagId = hierarchy.getTagId();// 区域标签
            Long areaId = null;
            if (enterpriseTagIds.contains(3L)) {
                areaId = hierarchy.getAreaId();// 区域id
            }
            String areaTag = "province_id";
            if (areaTagId == 1) {
                areaTag = "province_id";
            } else if (areaTagId == 2) {
                areaTag = "city_id";
            } else if (areaTagId == 3) {
                areaTag = "district_id";
            } else if (areaTagId == 4) {
                areaTag = "street_id";
            } else if (areaTagId == 5) {
                areaTag = "community_id";
            }

            // 2、产权单位，根据orgId查公租房
            /*List<String> rcIds = new ArrayList<>();
            if (enterpriseTagIds.contains(2L)) {
                List<House> houses = houseRepository.findByOrgId(enterpriseId);
                rcIds = houses.stream().map(House::getResidenceCommunityId).distinct().collect(Collectors.toList());//小区id
            }*/

            Long hierarchyOrgId = null;
            if (enterpriseTagIds.contains(2L) || enterpriseTagIds.contains(8L)) {
                hierarchyOrgId = hierarchy.getOrgId();
            }
            Long unitLesseeId = null;
            if (enterpriseTagIds.contains(7L)) {
                unitLesseeId = hierarchy.getOrgId();
            }

            // //3、物业，查已绑定该物业的小区下的所有房产
            List<String> residenceCommunityIds = new ArrayList<>();
            // if (enterpriseTagIds.contains(4L)) {
            //     List<Community> communities = communityRepository.findByOrgIdAndStatus(enterpriseId, 1);
            //     residenceCommunityIds = communities.stream().map(Community::getId).distinct().collect(Collectors.toList());//小区id
            // }

            // 当前登录用户自己所在企业以及下级企业是物业的
            /*List<Long> hierarchyOrgId = new ArrayList<>();
            List<OrgVO> org = propertyList(accessToken).getResultData();
            hierarchyOrgId = org.stream().map(OrgVO::getOrgId).collect(Collectors.toList());*/


            List<Map<String, Object>> communityList = appCommunityService.findCommunity(accessToken).getResultData();
            if (communityList != null && !communityList.isEmpty()) {
                residenceCommunityIds = communityList.stream().map(map -> map.get("communityId")
                        .toString()).collect(Collectors.toList());
            }


            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();

            // 拼接标签查询权限
            where.append(" where 1 = 1 and h.status = 1");
            where.append(" and (h." + areaTag + " = ? or h.org_id = ? or h.unit_lessee_id = ? or h.residence_community_id in ");
            args.add(areaId);
            args.add(hierarchyOrgId);
            args.add(unitLesseeId);
            if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
                where.append(SqlUtils.foreachIn(residenceCommunityIds.size()) + ")");
                args.addAll(residenceCommunityIds);
            } else {
                where.append("(null))");
            }

            where.append("  AND c.status = 1 ");

            // 查询条件
            if (vo != null) {
                // 房屋ID
                if (StringUtils.isNotBlank(vo.getId())) {
                    where.append(" and h.id like '%").append(vo.getId()).append("%'");
                }
                // 房屋ID集合
                if (houseIdList != null && !houseIdList.isEmpty()) {
                    where.append(" AND h.id in ").append(SqlUtils.foreachIn(houseIdList.size()));
                    args.addAll(houseIdList);
                }
                // 小区id
                if (StringUtils.isNotBlank(vo.getResidenceCommunityId())) {
                    where.append(" and h.residence_community_id = ?");
                    args.add(vo.getResidenceCommunityId());
                }
                // 楼宇id
                if (vo.getBuildingNum() != null) {
                    where.append(" and h.building_num = ?");
                    args.add(vo.getBuildingNum());
                }
                // 单元id
                if (vo.getUnit() != null) {
                    where.append(" and h.unit = ?");
                    args.add(vo.getUnit());
                }
                // 房号
                if (StringUtils.isNotBlank(vo.getRoomNumber())) {
                    where.append(" and h.room_number = ?");
                    args.add(vo.getRoomNumber());
                }
                // 小区名称
                if (StringUtils.isNotBlank(vo.getResidenceCommunityName())) {
                    where.append(" and c.name like ?");
                    args.add("%" + vo.getResidenceCommunityName() + "%");
                }

                // 楼层
                if (vo.getFloor() != null) {
                    where.append(" and h.floor = ?");
                    args.add(vo.getFloor());
                }

                // 面积(平方米)
                if (vo.getArea() != null) {
                    where.append(" and h.area = ?");
                    args.add(vo.getArea());
                }
                // 省
                if (vo.getProvinceId() != null) {
                    where.append(" and h.province_id = ?");
                    args.add(vo.getProvinceId());
                }
                // 市
                if (vo.getCityId() != null) {
                    where.append(" and h.city_id = ?");
                    args.add(vo.getCityId());
                }
                // 区
                if (vo.getDistrictId() != null) {
                    where.append(" and h.district_id = ?");
                    args.add(vo.getDistrictId());
                }
                // 街道
                if (vo.getStreetId() != null) {
                    where.append(" and h.street_id = ?");
                    args.add(vo.getStreetId());
                }
                // 社区
                if (vo.getCommunityId() != null) {
                    where.append(" and h.community_id = ?");
                    args.add(vo.getCommunityId());
                }
                // 小区地址
                if (StringUtils.isNotBlank(vo.getAddress())) {
                    where.append(" and h.address like ?");
                    args.add("%" + vo.getAddress() + "%");
                }
                // 所属物业
                if (vo.getHousePropertyId() != null) {
                    where.append(" and h.house_property_id = ?");
                    args.add(vo.getHousePropertyId());
                }
                // 产权所有人、产权单位
                if (StringUtils.isNotBlank(vo.getOwnerName())) {
                    where.append(" and (h.owner_name like ? or d.name like ?)");
                    args.add("%" + vo.getOwnerName() + "%");
                    args.add("%" + vo.getOwnerName() + "%");
                }
                // 户型室
                if (vo.getRoom() != null) {
                    where.append(" and h.room = ?");
                    args.add(vo.getRoom());
                }
                // 户型厅
                if (vo.getHall() != null) {
                    where.append(" and h.hall = ?");
                    args.add(vo.getHall());
                }
                // 户型卫
                if (vo.getToilet() != null) {
                    where.append(" and h.toilet = ?");
                    args.add(vo.getToilet());
                }

                // 性质
                if (vo.getHouseNature() != null) {
                    where.append(" and h.house_nature = ?");
                    args.add(vo.getHouseNature());
                }

                // 房屋管理员
                if (vo.getHouseManager() != null) {
                    where.append(" and t.name like ? ");
                    args.add("%" + vo.getHouseManager() + "%");
                }

                // 搜索关键字
                if (StringUtils.isNotBlank(vo.getSearch())) {
                    where.append(" and (h.id like ? or c.name like ? or b.name like ? or u.name like ? or h.room_number like ? or (h.owner_name like ? or d.name like ?) or h.owner_mobile like ?)  ");  // 搜索关键字字段【房屋id、小区、楼栋、单元、房号】
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                }
                // 出租状态  0未出租 1已出租
                if (vo.getLeaseStatus() != null) {
                    if (vo.getLeaseStatus() == 1) {
                       /* StringBuilder cBuf = new StringBuilder("(");
                        if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
                            for (int i = 0; i < residenceCommunityIds.size(); i++) {
                                if (i > 0) {
                                    cBuf.append(",");
                                }
                                cBuf.append("'").append(residenceCommunityIds.get(i)).append("'");
                            }
                            //cBuf.append("(").append(""+String.join(",",residenceCommunityIds)+"'");
                        } else {
                            cBuf.append("null");
                        }

                        where.append(" \t\t AND h.id in (select hh.id from \n" +
                                "\t (select h.id, CASE \n" +
                                "\t  WHEN ISNULL(uh.start_date) THEN\n" +
                                "\t\t0\n" +
                                "\t  ELSE\n" +
                                "\t\t1\n" +
                                "    END  tt \n" +
                                "    from saas_house h left join m_match_user_house uh on h.id = uh.house_id) hh where hh.tt = 1) \n" +
                                "\t \n" +
                                "\tUNION all\n" +
                                "\t SELECT h.*,c.name residenceCommunityName,b.name buildingNumName,b.type buildType,b.house_number houseNumber,u.name unitName,a1.name province,a2.name city,a3.name district,a4.name street,a5.name community,t.name houseManager    \n" +
                                "\t FROM saas_house h    \n" +
                                "\t LEFT JOIN saas_community c ON c.id=h.residence_community_id   \n" +
                                "\t LEFT JOIN saas_community_build b ON b.id=h.building_num   \n" +
                                "\t LEFT JOIN saas_community_build_unit u ON u.id=h.unit   \n" +
                                "\t LEFT JOIN sys_area a1 ON h.province_id=a1.id  \n" +
                                "   LEFT JOIN sys_area a2 ON h.city_id=a2.id  \n" +
                                "   LEFT JOIN sys_area a3 ON h.district_id=a3.id   \n" +
                                "\t LEFT JOIN sys_area a4 ON h.street_id=a4.id    \n" +
                                "\t LEFT JOIN sys_area a5 ON h.community_id=a5.id \n" +
                                "   LEFT JOIN saas_department d ON h.org_id = d.id  \n" +
                                "\t LEFT JOIN (SELECT * FROM saas_community_house_user schu\n" +
                                "   WHERE schu.status =1 and schu.type = 3) t\n" +
                                "   ON t.community_house_id = h.id\n" +
                               "\t where 1 = 1 and (h."+areaTag+" = "+areaId+" or h.org_id = "+hierarchyOrgId+" or h.residence_community_id in "+cBuf.toString()+"))\n" +
                                "\t AND c.status = 1 AND h.lease_status = 1 ");*/
                        where.append("  and (h.lease_status IS NULL or h.lease_status = 1)\n" +
                                "\t AND h.id in (select hh.id from \n" +
                                "\t (select h.id, CASE \n" +
                                "\t  WHEN ISNULL(uh.start_date) THEN\n" +
                                "\t\t0\n" +
                                "\t  ELSE\n" +
                                "\t\t1\n" +
                                "    END  tt \n" +
                                "    from saas_house h left join m_match_user_house uh on h.id = uh.house_id) hh where hh.tt = 1) \n");
                        // "\t  ORDER BY c.name,b.name,u.name,h.room_number ASC ");
                    } else if (vo.getLeaseStatus() == 0) {// 未出租
                        where.append("  and (h.lease_status IS NULL or h.lease_status = 0)\n" +
                                "\t AND h.id in (select hh.id from \n" +
                                "\t (select h.id, CASE \n" +
                                "\t  WHEN ISNULL(uh.start_date) THEN\n" +
                                "\t\t0\n" +
                                "\t  ELSE\n" +
                                "\t\t1\n" +
                                "    END  tt \n" +
                                "    from saas_house h left join m_match_user_house uh on h.id = uh.house_id) hh where hh.tt = 0) \n");
                        // "\t  ORDER BY c.name,b.name,u.name,h.room_number ASC ");
                    } else {
                        where.append("  and (h.lease_status IS NULL or h.lease_status = ? ) ");
                        args.add(vo.getLeaseStatus());
                    }

                }

                // 房屋管理员
                if (vo.getHouseManager() != null) {
                    where.append(" and t.name like ? ");
                    args.add("%" + vo.getHouseManager() + "%");
                }

                // 搜索关键字
                if (StringUtils.isNotBlank(vo.getSearch())) {
                    where.append(" and (h.id like ? or c.name like ? or b.name like ? or u.name like ? or h.room_number like ? or (h.owner_name like ? or d.name like ?) or h.owner_mobile like ?)  ");  // 搜索关键字字段【房屋id、小区、楼栋、单元、房号】
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                }
            }

            // 物业房产详情
            String SQL = "SELECT h.*,c.name residenceCommunityName,b.name buildingNumName,b.type buildType,b.house_number houseNumber,u.name unitName  " +
                    "   ,a1.name province,a2.name city,a3.name district,a4.name street,a5.name community,t.name houseManager  " +
                    "   FROM saas_house h  " +
                    "   LEFT JOIN saas_community c ON c.id=h.residence_community_id  " +
                    "   LEFT JOIN saas_community_build b ON b.id=h.building_num  " +
                    "   LEFT JOIN saas_community_build_unit u ON u.id=h.unit  " +
                    "   LEFT JOIN sys_area a1 ON h.province_id=a1.id  " +
                    "   LEFT JOIN sys_area a2 ON h.city_id=a2.id  " +
                    "   LEFT JOIN sys_area a3 ON h.district_id=a3.id  " +
                    "   LEFT JOIN sys_area a4 ON h.street_id=a4.id  " +
                    "   LEFT JOIN sys_area a5 ON h.community_id=a5.id " +
                    "   LEFT JOIN saas_department d ON h.org_id = d.id" +
                    "   LEFT JOIN (SELECT * FROM saas_community_house_user schu\n" +
                    "   WHERE schu.status =1 and schu.type = 3) t\n" +
                    "   ON t.community_house_id = h.id";

            ApiData<HouseVO> apiData = new ApiData<>();
            log.info(SQL + where);
            log.info(JSONObject.toJSONString(args.toArray()));
            log.info(String.valueOf(enterpriseId));

            String countsql = "SELECT COUNT(1) FROM (" + SQL + where + ") ss";

            // 物业房产数量
            Long total = jdbcTemplate.queryForObject(countsql, Long.class, args.toArray());
            if (total == 0) {
                return ResultBean.successfulResult(apiData);
            }
            String sqlt = "SELECT * FROM (" + SQL + where + ") ss";
            // 查询分页数据
            String offsets = " limit ? offset ?";
            StringBuffer order = new StringBuffer();
            order.append(" ORDER BY  ss.residenceCommunityName,ss.buildingNumName,ss.unitName,ss.room_number ASC ").append(" \n").append(offsets);

            args.add(pageSize);
            args.add(pageNumber * pageSize);

            // 房产总数据
            List<HouseVO> data = jdbcTemplate.query(sqlt + order, new BeanPropertyRowMapper<>(HouseVO.class), args.toArray());

            // 房产所属物业列表
            List<Long> housePropertyIdList = data.stream().map(HouseVO::getHousePropertyId).collect(Collectors.toList());
            // 物业对应机构层级
            List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findByOrgIdIn(housePropertyIdList);

            if (!departmentHierarchyList.isEmpty()) {

                for (HouseVO houseVO : data) {
                    for (DepartmentHierarchy departmentHierarchy : departmentHierarchyList) {
                        // if (houseVO.getHousePropertyId().equals(departmentHierarchy.getOrgId())) {
                        //    houseVO.setHouseProperty(departmentHierarchy.getName());
                        //}
                        if (departmentHierarchy.getOrgId().equals(houseVO.getHousePropertyId())) {
                            houseVO.setHouseProperty(departmentHierarchy.getName());
                        }
                    }

                    //    设置出租状态：0未出租 1已出租 2已过期 3退租中 4合租出租中 20230309 xiaolong
                    Integer leaseStatus = houseVO.getLeaseStatus();
                    if (leaseStatus == 0) {
                        houseVO.setLease("未出租");
                    }
                    if (leaseStatus == 1) {
                        houseVO.setLease("已出租");
                    }
                    if (leaseStatus == 3) {
                        houseVO.setLease("退租中");
                    }
                    if (leaseStatus == 4) {
                        houseVO.setLease("合租出租中");
                    }
                }
            }

            // 公租房查询产权单位名称
            List<Long> ownerIds = data.stream().filter(houseVO -> houseVO.getOrgId() != null).map(HouseVO::getOrgId).distinct().collect(Collectors.toList());
            if (!ownerIds.isEmpty()) {
                // 产权单位
                Map<Long, String> map = departmentRepository.findByIdIn(ownerIds).stream().collect(Collectors.toMap(Department::getId, Department::getName));
                if (!map.isEmpty()) {
                    data.forEach(houseVO -> {
                        // 设置产权所有人、产权单位
                        if (houseVO.getHouseNature() != null && houseVO.getHouseNature() == 1) {
                            houseVO.setOwnerName(map.get(houseVO.getOrgId()));
                            houseVO.setOrgName(map.get(houseVO.getOrgId()));
                        }
                    });
                }
            }

            // 查询楼栋名称
            List<Long> buildingIds = data.stream().filter(houseVO -> houseVO.getBuildingNum() != null).map(HouseVO::getBuildingNum).distinct().collect(Collectors.toList());
            if (!buildingIds.isEmpty()) {
                List<CommunityBuild> buildingList = communityBuildRepository.findByIdIn(buildingIds);
                // 楼栋层数
                Map<Long, Integer> map2 = buildingList.stream().filter(houseVO -> houseVO.getHouseNumber() != null).collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getHouseNumber));
                // 楼栋类型
                Map<Long, Integer> map3 = buildingList.stream().filter(houseVO -> houseVO.getType() != null).collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getType));
                if (!map2.isEmpty()) {
                    data.forEach(houseVO -> houseVO.setHouseNumber(map2.get(houseVO.getBuildingNum())));
                }
                if (!map3.isEmpty()) {
                    data.forEach(houseVO -> houseVO.setBuildType(map3.get(houseVO.getBuildingNum())));
                }
            }


            // 公租房
            List<String> hList = data.stream().filter(d -> d.getHouseNature() == 1).map(dd -> dd.getId()).collect(Collectors.toList());
            // 非公租房
            List<String> hdList = data.stream().filter(d -> d.getHouseNature() != 1).map(dd -> dd.getId()).collect(Collectors.toList());

            if (hList != null && !hList.isEmpty()) {
                // 公租房
                List<House> byHouseNature = houseRepository.findByHouseNatureAndIdIn(1, hList);
                // 设置公租房状态
                data.forEach(d -> {
                    byHouseNature.forEach(h -> {
                        if (d.getId().equals(h.getId())) {
                            if (h.getLeaseStatus() == null || h.getLeaseStatus() == 0) {
                                d.setLease("未出租");
                            } else if (h.getLeaseStatus() == 1 || h.getLeaseStatus() == 2) {
                                d.setLease("已出租");
                            }
                        }
                    });
                });
            }

            if (hdList != null && !hdList.isEmpty()) {
                // 非公租房   查询m_match_user_house  开始时间和结束时间不为空
                String sql1 = " SELECT m.* from m_match_user_house m\n" +
                        " LEFT JOIN saas_house h " +
                        " ON h.id = m.house_id ";
                StringBuffer where1 = new StringBuffer(" WHERE 1=1 ");

                where1.append(" and house_id in " + SqlUtils.foreachIn(hdList.size()));
                if (vo.getLeaseStatus() != null) {
                    if (vo.getLeaseStatus() == 0) {
                        where1.append(" and (m.start_date is NULL or m.end_date is NULL)");
                    } else if (vo.getLeaseStatus() == 1) {
                        where1.append(" and (m.start_date is TRUE or m.end_date is TRUE)");
                    }
                }

                // 查询出的已出租或未出租的房屋
                List<MatchUserHouseVO> matchUserHouseVOS = jdbcTemplate.query(sql1 + where1, new BeanPropertyRowMapper<>(MatchUserHouseVO.class), hdList.toArray());
                // 设定房屋出租状态
                // if (matchUserHouseVOS != null && !matchUserHouseVOS.isEmpty()){
                //    data.forEach(s -> {
                //        hdList.forEach(item -> {
                //            matchUserHouseVOS.forEach(h -> {
                //                if (item.equals(h.getHouseId())) {
                //                    if (s.getId().equals(h.getHouseId())){
                //                        if (h.getStartDate() == null || h.getEndDate() == null){
                //                            s.setLeaseStatus(0);
                //                            s.setLease("未出租");
                //                        }
                //                        if (h.getStartDate()!=null && h.getEndDate()!=null ){
                //                            s.setLeaseStatus(1);
                //                            s.setLease("已出租");
                //                        }
                //                    }
                //                }else {
                //                    if (item.equals(s.getId())){
                //                        if (s.getLeaseStatus() == null || s.getLeaseStatus() == 0){
                //                            s.setLease("未出租");
                //                            s.setLeaseStatus(0);
                //                        }else {
                //                            s.setLeaseStatus(1);
                //                            s.setLease("已出租");
                //                        }
                //                    }
                //                }
                //            });
                //        });
                //    });
                //}else {
                //    data.forEach(s -> {
                //        hdList.forEach(item ->{
                //            if (item.equals(s.getId())){
                //                if (s.getLeaseStatus() == null || s.getLeaseStatus() == 0){
                //                    s.setLease("未出租");
                //                    s.setLeaseStatus(0);
                //                }else {
                //                    s.setLeaseStatus(1);
                //                    s.setLease("已出租");
                //                }
                //            }
                //        });
                //    });
                //}
            }

            apiData.setTotal(total);
            apiData.setData(data);
            return ResultBean.successfulResult(apiData);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 当前登录用户自己所在企业以及下级企业是物业的房产
     *
     * @param accessToken
     * @return
     */
    public ResultBean<List<OrgVO>> propertyList(String accessToken) {

        // 从redis中取出登录信息
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        if (tokenRedisVo == null) {
            // 40001 --- accessToken失效
            return ResultBean.failedResultOfToken();
        }
        // 企业id
        Long enterpriseId = tokenRedisVo.getEnterpriseId();

        // 根据企业id查出机构层级关系
        DepartmentHierarchy departmentHierarchy = departmentHierarchyRepository.findByOrgId(enterpriseId);
        // 获取当前机构区域id
        Long areaId = departmentHierarchy.getAreaId();
        String sql = "SELECT dh.org_id AS orgId,dh.name AS orgName FROM `saas_department_hierarchy` dh  \n" +
                "left join saas_department_enterprise_tag_ref detr on dh.id = detr.dep_ref_id\n" +
                "WHERE dh.area_id = ? and detr.tag_id = 4";
        List<OrgVO> orgVO = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(OrgVO.class), areaId);
        /*//查找当前机构层级节点所有节点(不包含当前节点)
        List<Long> allChildHierarchy = allChildByHierarchy(departmentHierarchy.getId());
        log.info("CommunityHouseService propertyList allChildByHierarchy{}",allChildHierarchy);
        //当前机构所有层级节点的id查出机构层级关系
        List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findByIdIn(allChildHierarchy);
        //加入当前节点公司
        departmentHierarchyList.add(departmentHierarchy);
        //物业公司集合
        List<OrgVO> propertyList = new ArrayList<>();

        for (DepartmentHierarchy dh : departmentHierarchyList) {
            //查询出每个机构所属标签关系
            List<DepartmentEnterpriseTagRef> entityList = departmentEnterpriseTagRefRepository.findByDepRefId(dh.getId());
            //获取机构标签
            List<Long> tagIdList = entityList.stream().map(DepartmentEnterpriseTagRef::getTagId).collect(Collectors.toList());
            if (tagIdList.contains(4L)) {    //物业公司
                OrgVO orgVO = new OrgVO();
                orgVO.setOrgId(dh.getOrgId());
                orgVO.setOrgName(dh.getName());
                propertyList.add(orgVO);
            }
        }*/
        return ResultBean.successfulResult(orgVO);
    }


    // 查找当前机构层级节点所有节点(不包含当前节点)saas_department_hierarchy
    public List<Long> allChildByHierarchy(Long id) {
        String sql = " select id from ( select t1.id,if(find_in_set(parent_id, @pids) > 0,@pids := concat(@pids, ',', id),0) as ischild from ( select id,parent_id from `saas_department_hierarchy` t order by parent_id, id ) t1,(select @pids := ?) t2 ) t3 where ischild != 0 \n";
        return jdbcTemplate.queryForList(sql, new Object[]{id}, Long.class);
    }

    /**
     * 新增退租
     *
     * @param accessToken
     * @param vo
     * @return
     */
    @Transactional
    public ResultBean<Object> addLeaseWithdrawal(String accessToken, HouseSurrenderVO vo) {
        TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
        // 根据房屋id查找是否已添加过退租
        List<HouseSurrender> byHouseId = houseSurrenderRepository.findByHouseId(vo.getHouseId());
        if (byHouseId != null && !byHouseId.isEmpty()) {
            for (HouseSurrender houseSurrender : byHouseId) {
                if (houseSurrender.getStatus() != 6) {
                    return ResultBean.failedResultWithMsg("该房屋已添加退租申请");
                }
            }
        }
        Integer surreenderType = vo.getSurreenderType();
        if (surreenderType == null) {
            return ResultBean.failedResultWithMsg("请选择退租原因");
        }
        // 新增退租管理
        if (vo != null) {
            HouseSurrender houseSurrender = new HouseSurrender();
            String matchUserName = null;
            String matchUserPhone = null;
            // 获取承租人信息
            List<MatchUserHouse> byHouseIdList = matchUserHouseRepository.findByHouseId(vo.getHouseId());
            if (!byHouseIdList.isEmpty()) {
                Long matchUserStockId = Long.valueOf(byHouseIdList.get(0).getMatchUserStockId());
                Optional<MatchUser> byId = matchUserRepository.findById(matchUserStockId);
                if (byId.isPresent()) {
                    matchUserName = byId.get().getName();
                    matchUserPhone = byId.get().getPhone();
                }
            }
            houseSurrender.setMatchUserName(matchUserName);
            houseSurrender.setMatchUserPhone(matchUserPhone);
            houseSurrender.setHouseId(vo.getHouseId());
            houseSurrender.setStatus(1);
            houseSurrender.setSurreenderType(vo.getSurreenderType());
            houseSurrender.setRemark(vo.getRemark());
            houseSurrender.setOperatorId(tokenRedisVo.getId());
            houseSurrender.setOperatorTime(new Date());
            houseSurrenderRepository.save(houseSurrender);
            //    新增:点击退租时，修改房屋出租状态：0未出租 1已出租 2已过期 3退租中 4合租出租中 20230309 xiaolong
            String houseId = vo.getHouseId();
            List<House> byIdAndStatus = houseRepository.findByIdAndStatus(houseId, 1);
            if (!byIdAndStatus.isEmpty()) {
                House house = byIdAndStatus.get(0);
                house.setLeaseStatus(3);
                houseRepository.save(house);
            }

        }
        return ResultBean.successfulResultWithMsg("生成退租单", null);
    }


    /**
     * 添加房屋管理员
     *
     * @param accessToken
     * @return 判断当前的房屋是否有承租人 有承租人则清空承租人信息 无承租人添加房屋管理员
     */
    public ResultBean<Object> addHouseManager(String accessToken, SaveOrEditVO saveOrEditVO) {
        try {
            if (saveOrEditVO.getHouseIds() != null) {
                String[] split = saveOrEditVO.getHouseIds().split(",");
                for (String s : split) {
                    // 查询正常状态的房屋管理员的房产成员信息登记表明细
                    List<CommunityHouseUser> communityHouseUsers = communityHouseUserRepository.findByCommunityHouseIdAndStatusAndType(s, 1, 3);
                    saveOrEditVO.setMatchUserHouseVO(null);
                    // 根据房屋id查到小区id
                    // 获取第一个房产设置房产id
                    saveOrEditVO.getCommunityHouseUsers().get(0).setCommunityHouseId(s);
                    Optional<House> byId = houseRepository.findById(s);
                    // 设置小区id
                    saveOrEditVO.getCommunityHouseUsers().get(0).setCommunityId(byId.get().getResidenceCommunityId());
                    // 根据前端传来的用户id，查到手机号
                    String mobile = userRepository.findById(saveOrEditVO.getCommunityHouseUsers().get(0).getUserId()).getMobile();
                    saveOrEditVO.getCommunityHouseUsers().get(0).setMobile(mobile);

                    if (communityHouseUsers != null && !communityHouseUsers.isEmpty()) {
                        for (CommunityHouseUser communityHouseUser : communityHouseUsers) {
                            if (!communityHouseUser.getUserId().equals(saveOrEditVO.getCommunityHouseUsers().get(0).getUserId())) {
                                // 当前房屋管理员和前端传来的的房屋管理员不一致时
                                // 清除承租人信息
                                communityHouseUserService.delete(accessToken, communityHouseUser.getId());
                                // 新增房屋管理员
                                communityHouseUserService.saveHouseManager(accessToken, saveOrEditVO);
                            }
                        }
                    } else {
                        // 修改房屋管理员
                        communityHouseUserService.saveHouseManager(accessToken, saveOrEditVO);
                    }
                }
                return ResultBean.successfulResult(null);
            }
            return ResultBean.failedResultWithMsg("未选择房屋");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

}
